const { pool } = require('../config/db');
const { NotFoundError } = require('../middleware/error');

class ProductModel {
  // 构建基础查询SQL
  static getBaseQuery() {
    return `
      SELECT 
        p.id, p.category_id, p.name, p.slug, p.description,
        p.short_description, p.price, p.original_price,
        p.stock_quantity, p.sku, p.thumbnail_url,
        p.is_active, p.is_recommended, p.created_at, p.updated_at,
        c.name as category_name, c.slug as category_slug
      FROM products p
      LEFT JOIN categories c ON p.category_id = c.id
      WHERE p.is_active = 1
    `;
  }

  // 构建排序SQL
  static getSortSQL(sort_by = 'created_at', sort_order = 'desc') {
    const allowedSortFields = ['created_at', 'price', 'name', 'is_recommended'];
    const allowedSortOrders = ['asc', 'desc'];
    
    // 验证并清理排序参数
    sort_by = allowedSortFields.includes(sort_by) ? sort_by : 'created_at';
    sort_order = allowedSortOrders.includes(sort_order.toLowerCase()) ? sort_order.toLowerCase() : 'desc';
    
    return ` ORDER BY p.${sort_by} ${sort_order}`;
  }

  // 获取商品列表
  static async findAll({
    page = 1,
    limit = 12,
    category_id = null,
    category_slug = null,
    search = null,
    sort_by = 'created_at',
    sort_order = 'desc',
    is_recommended = null
  }) {
    try {
      // 确保参数类型正确
      const pageNum = Math.max(1, parseInt(page) || 1);
      const limitNum = Math.max(1, Math.min(50, parseInt(limit) || 12)); // 限制每页最大条数为50
      const offset = (pageNum - 1) * limitNum;

      let sql = this.getBaseQuery();
      const params = [];
      
      // 添加筛选条件
      if (category_id) {
        sql += ' AND p.category_id = ?';
        params.push(parseInt(category_id));
      }
      
      if (category_slug) {
        sql += ' AND c.slug = ?';
        params.push(String(category_slug));
      }
      
      if (search) {
        sql += ' AND (p.name LIKE ? OR p.short_description LIKE ?)';
        const searchPattern = `%${String(search)}%`;
        params.push(searchPattern, searchPattern);
      }
      
      if (is_recommended !== null) {
        sql += ' AND p.is_recommended = ?';
        params.push(is_recommended ? 1 : 0);
      }
      
      // 添加排序
      sql += this.getSortSQL(sort_by, sort_order);
      
      // 添加分页
      sql += ' LIMIT ?, ?';
      params.push(offset, limitNum);

      // 执行查询
      const [rows] = await pool.query(sql, params);
      
      // 获取总数
      let countSql = 'SELECT COUNT(*) as total FROM products p LEFT JOIN categories c ON p.category_id = c.id WHERE p.is_active = 1';
      const countParams = [];

      if (category_id) {
        countSql += ' AND p.category_id = ?';
        countParams.push(parseInt(category_id));
      }
      if (category_slug) {
        countSql += ' AND c.slug = ?';
        countParams.push(String(category_slug));
      }
      if (search) {
        countSql += ' AND (p.name LIKE ? OR p.short_description LIKE ?)';
        const searchPattern = `%${String(search)}%`;
        countParams.push(searchPattern, searchPattern);
      }
      if (is_recommended !== null) {
        countSql += ' AND p.is_recommended = ?';
        countParams.push(is_recommended ? 1 : 0);
      }
      
      const [countRows] = await pool.query(countSql, countParams);
      const total = parseInt(countRows[0].total);
      
      return {
        items: rows,
        pagination: {
          total,
          page: pageNum,
          limit: limitNum,
          total_pages: Math.ceil(total / limitNum)
        }
      };
    } catch (error) {
      console.error('Error in findAll:', error);
      throw error;
    }
  }

  // 获取单个商品详情
  static async findOne(idOrSlug) {
    // 基础查询
    let sql = this.getBaseQuery();
    
    // 判断是ID还是slug
    const isId = !isNaN(idOrSlug);
    sql += isId ? ' AND p.id = ?' : ' AND p.slug = ?';
    
    // 执行查询
    const [rows] = await pool.query(sql, [idOrSlug]);
    
    if (rows.length === 0) {
      throw new NotFoundError('商品不存在');
    }
    
    const product = rows[0];
    
    // 获取商品图片
    const [images] = await pool.query(
      `SELECT id, image_url, alt_text, is_default, sort_order
       FROM product_images
       WHERE product_id = ?
       ORDER BY sort_order ASC, id ASC`,
      [product.id]
    );
    
    product.images = images;
    
    return product;
  }

  // 检查商品库存
  static async checkStock(productId, quantity) {
    const [rows] = await pool.query(
      'SELECT stock_quantity FROM products WHERE id = ? AND is_active = 1',
      [productId]
    );
    
    if (rows.length === 0) {
      throw new NotFoundError('商品不存在');
    }
    
    return {
      available: rows[0].stock_quantity >= quantity,
      current_stock: rows[0].stock_quantity
    };
  }

  // 更新商品库存
  static async updateStock(productId, quantity, increment = false) {
    const operator = increment ? '+' : '-';
    const [result] = await pool.query(
      `UPDATE products 
       SET stock_quantity = stock_quantity ${operator} ?
       WHERE id = ? AND is_active = 1 AND stock_quantity ${increment ? '>= 0' : '>= ?'}`,
      increment ? [quantity, productId] : [quantity, productId, quantity]
    );
    
    if (result.affectedRows === 0) {
      throw new Error('库存更新失败');
    }
    
    return true;
  }
}

module.exports = ProductModel; 